﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Vroom.Common;
using Vroom.Common.Config;
using Vroom.Common.Enums;
using Vroom.DataAccessLayer.Model;
using LumenWorks.Framework.IO.Csv;

namespace Vroom.Config
{
    public class ConfigUtils : IConfigUtils
    {
        public string GetPreviousWorkingDayBySourceSystem(SourceSystem sourceSystem)
        {
            ObjectParameter outputParameter;

            using (DWHEntities dwhEnts = new DWHEntities())
            {
                outputParameter = new ObjectParameter("PreviousWorkingDayDate", typeof(string));
                int tminus1 = dwhEnts.usp_GetPreviousWorkingDayBySystem("FIN07", outputParameter);
            }

            return outputParameter.Value.ToString();
        }


        public string GetColumnMappings(IConfigItem configItem)
        {
            string columnMappings = string.Empty;
            StringBuilder sb = new StringBuilder();
            Dictionary<string, Type> colDef = GetExtendedDestinationSchema(configItem, GetExplicitNullColumns(configItem));

            sb.Append("<Mappings>");
            using (CsvReader csv = new CsvReader(new StreamReader(configItem.Source, Encoding.GetEncoding(1252)), true, colDef))
            {
                int colsIndex=0;
                string[] destCols = colDef.Keys.ToArray();                

                foreach (string source in csv.GetFieldHeaders())
                    sb.Append(@"<Column Source=""" + source + @"""" +  @" Destination=""" + destCols[colsIndex++] + @"""/>");
                
            }
            sb.Append("</Mappings>");
                         
            return sb.ToString();
        }


        public List<string> GetDestinationSchema(IConfigItem configItem)
        {
            DataTable schema = null;
            List<string> tableColumns = new List<string>();

            using (var conn = new SqlConnection(configItem.DestinationConnectionString))
            {
                using (var cmd = new SqlCommand(Consts.SQL_SELECT_ALL + configItem.Destination, conn))
                {
                    conn.Open();
                    using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
                    {
                        schema = reader.GetSchemaTable();
                        tableColumns = schema.Rows.Cast<DataRow>()
                                                  .Where(p => !p.Field<string>("ColumnName").StartsWith("DateRecord") && !p.Field<string>("ColumnName").StartsWith("DateData"))
                                                  .Select(x => x.Field<string>("ColumnName"))
                                                  .ToList<string>();
                    }
                }
            }

            return tableColumns;
        }


        public Dictionary<string, Type> GetExtendedDestinationSchema(IConfigItem configItem, List<string> explicitNullColumns)
        {
            DataTable schema = null;
            Dictionary<string, Type> tableColumns = new Dictionary<string, Type>();

            using (var conn = new SqlConnection(configItem.DestinationConnectionString))
            {
                using (var cmd = new SqlCommand(Consts.SQL_SELECT_ALL + configItem.Destination, conn))
                {
                    conn.Open();
                    using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
                    {
                        schema = reader.GetSchemaTable();
                        tableColumns = schema.AsEnumerable()
                                         .Where(p => !p.Field<string>("ColumnName").StartsWith("DateRecord") && !p.Field<string>("ColumnName").StartsWith("DateData"))
                                         .ToDictionary(p => p.Field<string>("ColumnName"), p => p.Field<Type>("DataType"));
                    }
                }
            }

            #region Override fields as NULLs

            if (explicitNullColumns != null && explicitNullColumns.Count > 0)
            {
                if (explicitNullColumns.Count == 1 && explicitNullColumns[0] == Consts.ALL_STG_TBL)
                {
                    Dictionary<string, Type> setAllAsNULL = new Dictionary<string, Type>();
                    foreach (string colDefname in tableColumns.Keys)
                    {
                        setAllAsNULL.Add(colDefname, null);
                    }
                    tableColumns = setAllAsNULL;
                }
                else
                {
                    foreach (string col in explicitNullColumns)
                        if (tableColumns.ContainsKey(col))
                            tableColumns[col] = null;
                }
            }

            #endregion

            return tableColumns;
        }


        public List<string> GetExplicitNullColumns(IConfigItem configItem)
        {
            if (configItem.ExplicitNullColumns == null)
                return null;

            List<string> explicitNullColumns = new List<string>();
            XDocument xml;

            using (StringReader str = new StringReader(configItem.ExplicitNullColumns))
            {
                xml = XDocument.Load(str);

                List<string> g = xml.Descendants("Column").Attributes("Name").Select(p => p.Value).ToList();

                if (xml.Descendants("Column").Count() > 0)
                {
                    explicitNullColumns.AddRange(xml.Descendants("Column").Attributes("Name").Select(p => p.Value));
                }
            }

            return explicitNullColumns;
        }


        public Dictionary<string, string> GetColumnsOverrides(IConfigItem configItem)
        {
            XDocument xml;
            Dictionary<string, string> columnOverrides = new Dictionary<string, string>();

            using (StringReader str = new StringReader(configItem.MappingsOverrides))
            {
                xml = XDocument.Load(str);
                foreach (XElement elem in xml.Descendants("Column"))
                {
                    if (elem.Attributes().Where(p => p.Name == "Source").Count() > 0 && elem.Attributes().Where(p => p.Name == "Destination").Count() > 0)
                    {
                        columnOverrides.Add(elem.Attribute("Source").Value, elem.Attribute("Destination").Value);
                    }
                }
            }
            return columnOverrides;
        }


        public Dictionary<string, Tuple<Type, int, int, int>> GetDestinationSchemaMetadata(IConfigItem configItem)
        {            
            Dictionary<string, Tuple<Type, int, int, int>> tableColumns = new Dictionary<string, Tuple<Type, int, int, int>>();
            Dictionary<string, string> columnOverrides = GetColumnsOverrides(configItem);

            using (var conn = new SqlConnection(configItem.DestinationConnectionString))
            {
                using (var cmd = new SqlCommand(Consts.SQL_SELECT_ALL + configItem.Destination, conn))
                {
                    conn.Open();
                    using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
                    {
                        var rows = reader.GetSchemaTable().AsEnumerable()
                                                          .Where(p => !p.Field<string>("ColumnName").StartsWith("DateRecord") && !p.Field<string>("ColumnName").StartsWith("DateData"));
                        foreach (DataRow row in rows)
                        {
                            string fieldName = row.Field<string>("ColumnName");

                            if (columnOverrides.Count > 0 && columnOverrides.ContainsValue(fieldName))
                                fieldName = columnOverrides.Where(p => p.Value == fieldName).FirstOrDefault().Key;

                            tableColumns.Add
                            (
                                fieldName,
                                new Tuple<Type, int, int, int>(row.Field<Type>("DataType"), row.Field<int>("ColumnSize"), row.Field<Int16>("NumericPrecision"), row.Field<Int16>("NumericScale"))
                            );
                        }
                    }
                }
            }
            return tableColumns;
        }

    }
}